package sys.bi.service.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import sys.bi.page.model.Cost;
import sys.bi.page.model.DataGrid;
import sys.bi.page.model.PrizeOfDept;
import sys.bi.service.CostService;
import sys.market.service.impl.GroupServiceImpl;
import sys.util.CommonUtil;

@Repository("costService")
public class CostServiceImpl implements CostService {
	private static final Logger logger = Logger.getLogger(GroupServiceImpl.class);
	private SessionFactory sessionFactory;
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	@Autowired
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	@Override
	public Cost add(Cost cost) throws Exception {
		String sql = "";
		Query query = null;
		int total = 0;
		sql = "if exists (select 1 from BI_FinanicalItemIncome a,BI_Department b"
			+ " where a.departNo=b.depId and b.itemId='660103' and a.Date=:date and a.departNo=:deptno)"
			+ " select 1 else select 0";
		try {
			query = sessionFactory.getCurrentSession().createSQLQuery(sql);
			query.setTimestamp("date", CommonUtil.firstOfMonth(cost.getMonth()));
			query.setString("deptno", cost.getDepartmentCode());
			total = (Integer) query.uniqueResult();
		}catch(Exception ex) {
			ex.printStackTrace();
		}
		if (total==1) {
			sql = "update b set set amount=:amount"
				+ " BI_FinanicalItemIncome a"
				+ " where a.Date=:date and a.departNo=:deptno and a.itemId='660103'";
		} else {
			sql = "insert into BI_FinanicalItemIncome(itemId,itemName,departNo,amount,date)"
				+ " select itemId,itemName,:deptno,:amount,:date"
				+ " from BI_SubFinancialItem where itemId='660103'";
		}
		logger.info(CommonUtil.firstOfMonth(cost.getMonth()));
		try {
			query = sessionFactory.getCurrentSession().createSQLQuery(sql);
			query.setTimestamp("date", CommonUtil.firstOfMonth(cost.getMonth()));
			query.setString("amount", Double.toString(cost.getCost()));
			query.setString("deptno", cost.getDepartmentCode());
			query.executeUpdate();
		} catch(Exception ex) {
			ex.printStackTrace();
		}
		return cost;
	}
	@Override
	public Cost update(Cost cost) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public DataGrid list(Cost cost) {
		DataGrid grid = new DataGrid();
		String sql = "";
		Query query = null;
		//sql = "select convert(varchar(16),c.DeptNo) 'DeptNo',convert(varchar(32),c.DeptName) 'DeptName',convert(varchar(12),case when b.Amount='' then '0.00' else b.Amount end) 'Amount'"
		//    + " from InComeSet a,IncomeDetailSet b,DepartmentSet c"
		//	+ " where a.Date=:date and a.FileId=b.FileId and b.DeptNo=c.DeptNo and b.ItemId='99'";
		sql = "select depId,depName,b.amount"
			+ " from BI_Department a,BI_FinanicalItemIncome b"
			+ " where a.depId=b.departNo and b.date=:date and b.itemId='660103'";
		List<Object[]> data = null;
		try {
			query = sessionFactory.getCurrentSession().createSQLQuery(sql);
			query.setTimestamp("date", CommonUtil.firstOfMonth(cost.getMonth()));
			data = query.list();
		} catch(Exception ex) {
			ex.printStackTrace();		
			return null;
		}
		List<Cost> costList = new ArrayList<Cost>();
		for(Object[] obj : data) {
			Cost c = new Cost();
			c.setMonth(cost.getMonth());
			c.setDepartmentCode(obj[0].toString().trim());
			c.setDepartmentName(obj[1].toString().trim());
			c.setCost(Double.valueOf(obj[2].toString().trim()));
			costList.add(c);
		}
		grid.setRows(costList);
		return grid;
	}
	@Override
	public DataGrid selectDepartment(Cost cost) throws Exception {
		DataGrid grid = new DataGrid();
		String sql = "";
		Query query = null;
		sql = "SELECT depId,depName"
			+ " FROM BI_Department where depId like :DeptNo";
		List<Object[]> data = null;
		try {
			query = sessionFactory.getCurrentSession().createSQLQuery(sql);
			if (cost.getDepartmentCode() == null || cost.getDepartmentCode().trim().equals("")) {
				query.setString("DeptNo", "%%");
			} else {
				try {
					query.setString("DeptNo", "%"+cost.getDepartmentCode().trim()+"%");
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			data = query.list();
		} catch(Exception ex) {
			ex.printStackTrace();		
			return null;
		}
		List<Cost> costList = new ArrayList<Cost>();
		for(Object[] obj : data) {
			Cost c = new Cost();
			c.setMonth(cost.getMonth());
			c.setDepartmentCode(obj[0].toString().trim());
			c.setDepartmentName(obj[1].toString().trim());
			costList.add(c);
		}
		grid.setRows(costList);
		return grid;
	}
	@Override
	public DataGrid ksjx(Cost cost) throws Exception {
		DataGrid grid = new DataGrid();
		String sql = "";
		Query query = null;
		sql = "exec usp_bi_ksjx @month=:month";
		List<Object[]> data = null;
		try {
			query = sessionFactory.getCurrentSession().createSQLQuery(sql);
			query.setTimestamp("month", CommonUtil.firstOfMonth(cost.getMonth()));
			data = query.list();
		} catch(Exception ex) {
			ex.printStackTrace();		
			return null;
		}
		List<PrizeOfDept> prizeList = new ArrayList<PrizeOfDept>();
		for(Object[] obj : data) {
			PrizeOfDept p = new PrizeOfDept();
			p.setDeptNo(obj[0].toString().trim());
			p.setDeptName(obj[1].toString().trim());
			p.setIncome(Double.valueOf(obj[2].toString().trim()));
			p.setCost(Double.valueOf(obj[3].toString().trim()));
			p.setPrize(Double.valueOf(obj[4].toString().trim()));
			p.setBl(Double.valueOf(obj[5].toString().trim()));
			prizeList.add(p);
		}
		grid.setRows(prizeList);
		return grid;
	}
}
